package dao.impl;

import dao.BookDao;
import domain.Book;
import domain.Chapter;
import domain.Word;
import util.JDBCUtils;

import java.sql.*;
import java.util.*;
import java.util.Date;

public class BookDaoImpl implements BookDao {
    @Override
    public List<Chapter> getCapterNameList(int bid) {
        Connection connection =null;
        Statement statement =null;
        ResultSet resultSet;
        List<Chapter> bookList = new ArrayList<>();
        try {
            connection = JDBCUtils.getConnection();
            statement= connection.createStatement();
            resultSet= statement.executeQuery("SELECT name,cid FROM chapter WHERE bid="+bid);
            while (resultSet.next()){
                bookList.add(
                        new Chapter()
                                .setName(resultSet.getString("name"))
                                .setCid(resultSet.getInt("cid"))
                                .setBid(bid)
                );

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(statement,connection);
        }
        return bookList;
    }

    @Override
    public Chapter getCapter(int cid) {
        Connection connection =null;
        Statement statement =null;
        ResultSet resultSet;
        Chapter chapter =null;
        try {
            connection = JDBCUtils.getConnection();
            statement= connection.createStatement();
            resultSet= statement.executeQuery("SELECT name,content,bid FROM chapter WHERE cid="+cid);
            if (resultSet.next()){
                chapter = new Chapter()
                            .setName(resultSet.getString("name"))
                            .setCid(cid)
                            .setBid(resultSet.getInt("bid"))
                            .setContent(resultSet.getString("content"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(statement,connection);
        }
        return chapter;
    }

    @Override
    public List<Book> getCommonBook() {
        Connection connection =null;
        Statement statement =null;
        ResultSet resultSet;
        List<Book> bookList =new ArrayList<>();
        try {
            connection = JDBCUtils.getConnection();
            statement= connection.createStatement();
            resultSet= statement.executeQuery("SELECT bid,cover,NAME,brief,creation_time FROM book WHERE uid=0");
            while (resultSet.next()){
                //int bid, int uid, String cover, String name, String brief, Date createionTime
                bookList.add(new Book(
                        resultSet.getInt("bid"),
                        0,
                        resultSet.getString("cover"),
                        resultSet.getString("name"),
                        resultSet.getString("brief"),
                        resultSet.getDate("creation_time")
                ));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(statement,connection);
        }
        return bookList;
    }
    @Override
    public void addBookWord(String word, int frequency, int bid) {
        Connection connection =null;
        Statement statement =null;
        Map<String, Date> map = new TreeMap<>();
        try {
            connection = JDBCUtils.getConnection();
            String sql =String.format("INSERT INTO word(word,bid,frequency) VALUES('%s',%d,%d)",word,bid,frequency);
            statement= connection.createStatement();
            statement.execute(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(statement,connection);
        }
    }

    @Override
    public void addBook(String cover, String name, String brief, int uid) {
        Connection connection =null;
        PreparedStatement statement =null;
        Map<String, Date> map = new TreeMap<>();
        try {
            connection = JDBCUtils.getConnection();
            //创建sql 防止sql注入 添加sql中参数
            String sql ="INSERT INTO book(cover,name,brief,creation_time,uid) VALUES(?,?,?,?,?)";
            statement= connection.prepareStatement(sql);
            statement.setString(1,cover);
            statement.setString(2,name);
            statement.setString(3,brief);
            statement.setDate(4,new java.sql.Date(System.currentTimeMillis()));
            statement.setInt(5,uid);


            statement.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(statement,connection);
        }
    }

    @Override
    public void addCapter(int bid, String name, String content) {
        Connection connection =null;
        Statement statement =null;
        Map<String, Date> map = new TreeMap<>();
        try {
            connection = JDBCUtils.getConnection();
            String sql ="INSERT INTO chapter(bid,name,content) VALUES("+bid+",'"+name+"','"+content+"')";
            statement= connection.createStatement();
            statement.execute(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(statement,connection);
        }
    }

    @Override
    public void delBook(int bid) {
        Connection connection =null;
        Statement statement =null;
        Map<String, Date> map = new TreeMap<>();
        try {
            connection = JDBCUtils.getConnection();
            String sql ="DELETE FROM book WHERE bid="+bid;
            statement= connection.createStatement();
            statement.execute(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(statement,connection);
        }
    }

    @Override
    public void delCapter(int cid) {
        Connection connection =null;
        Statement statement =null;
        Map<String, Date> map = new TreeMap<>();
        try {
            connection = JDBCUtils.getConnection();
            String sql ="DELETE FROM chapter WHERE cid="+cid;
            statement= connection.createStatement();
            statement.execute(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(statement,connection);
        }
    }

    @Override
    public void modifyBook(int bid, String cover, String name, String brief) {
        Connection connection =null;
        Statement statement =null;
        Map<String, Date> map = new TreeMap<>();
        try {
            connection = JDBCUtils.getConnection();
            //update 表名 set 列名1 = 值1, 列名2 = 值2,... [where 条件];
            String sql ="UPDATE book SET cover='"+cover+"', name='"+name+"', brief='"+brief+"' WHERE bid="+bid;
            statement= connection.createStatement();
            statement.execute(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(statement,connection);
        }
    }

    @Override
    public Book getBookbybid(int bid) {
        Connection connection =null;
        Statement statement =null;
        ResultSet resultSet;
        Book book =null;
        try {
            connection = JDBCUtils.getConnection();
            statement= connection.createStatement();
            resultSet= statement.executeQuery("SELECT cover,name,brief,creation_time,uid FROM book WHERE bid="+bid);
            if (resultSet.next()){
                //int bid, int uid, String cover, String name, String brief, Date createionTime
                book =new Book(bid,resultSet.getInt("uid"),
                        resultSet.getString("cover"),
                        resultSet.getString("name"),
                        resultSet.getString("brief"),
                        resultSet.getDate("creation_time"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(statement,connection);
        }
        return book;
    }

    @Override
    public void addBookWord(List<Word> wordList) {
        Connection connection = null;
        Statement statement =null;
        try{
            connection =  JDBCUtils.getConnection();
            statement =  connection.createStatement();
            String sql;
            for(Word word : wordList){
                sql ="INSERT INTO word(word,bid,frequency) VALUES('"+word.getWord()+"',"+word.getBid()+","+word.getFrequency()+")";
                statement.execute(sql);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            JDBCUtils.close(statement,connection);
        }
    }

    @Override
    public void delBookWord(int bid) {
        Connection connection =null;
        Statement statement =null;
        Map<String, Date> map = new TreeMap<>();
        try {
            connection = JDBCUtils.getConnection();
            String sql ="DELETE FROM word WHERE bid="+bid;
            statement= connection.createStatement();
            statement.execute(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(statement,connection);
        }
    }
}
